use axum::{ extract::Query, Json };
use com::{ code, query, sea_orm::connect_db};
use sea_orm::{query::*, FromQueryResult, DbBackend, DbErr };

use serde::{ Deserialize, Serialize };
#[derive(Debug, Deserialize, Serialize, FromQueryResult)]
pub struct Data {
    version: String,
    applied_at: i64
}

pub async fn list(Query(param): Query<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&param,"database")?;
    let sort = query::str_op(&param,"sort")?;
    let sort_type = query::str_op(&param,"sort_type")?;
    let conn = connect_db(&dbname).await?;
    let table_name = query::str_op(&param, "table_name")?;
    let clear = query::u8_op(&param, "clear")?;
    let clear = if let Some(f) = clear { format!(" clear={} and ", f) } else {"".to_owned()};
    let stmt = Statement::from_string(
        conn.get_database_backend(),
        format!(
            "select * from seaql_migrations where{} version like '%{}%' order by (top=1) desc, {} {}",
            clear,
            &table_name.unwrap_or_default(),
            sort.unwrap_or("applied_at".to_owned()),
            sort_type.unwrap_or("desc".to_owned())
        ),
    );
    code::send_arr( Data::find_by_statement(stmt).into_model().all(&conn).await )
}
pub async fn update(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;

    let new_table_name = query::str(&body, "new_table_name")?;
    let table_name = query::str(&body, "table_name")?;

    let stmt = Statement::from_string(
        conn.get_database_backend(),
        format!(
            "update seaql_migrations set version='{}' where version='{}' ", 
            &new_table_name,
            &table_name
        ),
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn top(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;

    let table_name = query::str(&body, "table_name")?;
    let top = query::u8(&body, "top")?;

    let stmt = Statement::from_string(
        conn.get_database_backend(),
        format!(
            "update seaql_migrations set top={} where version='{}' ", 
            top,
            &table_name
        ),
    );
    code::send_ok( conn.execute(stmt).await )
}
pub async fn empty(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;

    let clear = query::u8(&body, "clear")?;
    let mut ids = query::vec_str(&body, "id")?;
    
    for (i, v) in ids.to_owned().iter().enumerate() {
        ids[i] = format!("version='{}'", v);
    }

    let ids = ids.join(" or ");

    let sql = format!("update seaql_migrations set clear={} where {}",  clear, ids);

   

    let stmt = Statement::from_string(conn.get_database_backend(),sql);
    code::send_ok( conn.execute(stmt).await )
}
pub async fn del(Json(body):Json<JsonValue>) -> Result<Json<JsonValue>, Json<JsonValue>>{
    let dbname = query::str(&body,"database")?;
    let conn = connect_db(&dbname).await?;
    let ids = query::vec_str(&body,"id")?;
    if ids.is_empty(){
        return code::fail(300) 
    }else{
        let transaction = conn.transaction::<_, (), DbErr>(
            |txn|{
                Box::pin(async move {
                    let mut x = "".to_string();
                    for v in &ids{
                        x = x + "'" + v + "'" + ","
                    }
                    // 删除表数据
                    let stmt = Statement::from_string(
                        DbBackend::MySql, 
                        format!("delete from seaql_migrations where version in({})", x.strip_suffix(",").unwrap())
                    );
                    txn.execute(stmt).await?;
                    // 删除表
                    for v in ids.clone() {
                        let stmt = Statement::from_string(
                            DbBackend::MySql,
                            format!("drop table if exists {}", &v)
                        );
                        txn.execute(stmt).await?;
                    }
                    
                    Ok(())
                })
            }
        ).await;
        code::send_ok(transaction)
    }
}